﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.BusinessData.Infrastructure;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.MetadataModel.Collections;
using Microsoft.BusinessData.Runtime;
using Microsoft.SharePoint.Client;

namespace SDS.SPConnectors.SPList
{
    public class SPListConnector : IAdministrableSystem, ISystemUtility, IConnectionManager, IDisposable
    {
        private class Configuration
        {
            public struct Field
            {
                public string DisplayName { get; set; }
                public string StaticName { get; set; }
                //public Guid Id { get; set; }
                public string LocalizedName { get; set; }

                public string FormatString { get; set; }
            }
            public System.Net.ICredentials Credentials { get; set; }
            public string SiteUrl { get; set; }
            public string ListUrl { get; set; }
            public List<Field> Fields { get; set; }
            public Dictionary<IFilter, string> Filters { get; set; }         // key is the MethodInstance filter, value is the filter value

            public Int32? Id { get; set; }

            private ClientContext _ClientContext;
            public ClientContext ClientContext
            {
                get
                {
                    if (_ClientContext == null)
                    {
                        _ClientContext = new Microsoft.SharePoint.Client.ClientContext(SiteUrl);

                        //Set security credentials
                        _ClientContext.Credentials = Credentials;
                    }
                    return _ClientContext;
                }
            }

            private List _List;
            public List List
            {
                get
                {
                    if (_List == null)
                    {
                        Microsoft.SharePoint.Client.ClientContext ctx = ClientContext;

                        //Get all lists to enable identification by URL
                        var oLists = ctx.Web.Lists;
                        ctx.Load(oLists);
                        ctx.ExecuteQuery();

                        //Get the list
                        _List = oLists.ToList().Single(x => x.DefaultViewUrl
                                                                 .Replace(SiteUrl, String.Empty)
                                                                 .Contains(new StringBuilder()
                                                                               .Append("/")
                                                                               .Append(ListUrl)
                                                                               .Append("/")
                                                                               .ToString()));
                    }
                    return _List;
                }
            }

            private List<Microsoft.SharePoint.Client.Field> _List_Fields;
            public List<Microsoft.SharePoint.Client.Field> List_Fields
            {
                get
                {
                    if (_List_Fields == null)
                    {
                        //Create Client Context
                        Microsoft.SharePoint.Client.ClientContext ctx = ClientContext;

                        //Get list information for the list
                        List oList = List;

                        //Load list fields into the client context
                        ctx.Load(oList.Fields);

                        //Execute query to get list items
                        ctx.ExecuteQuery();

                        _List_Fields = oList.Fields.ToList();
                    }

                    return _List_Fields;
                }
            }

            public string Fields_ToCAML()
            {
                return String.Join(String.Empty,
                                   Fields
                                          .Where(bdcmField => List_Fields.Any(x => x.StaticName == bdcmField.StaticName))
                                          .Select(field => String.Format("<FieldRef Name='{0}' />", field.StaticName))
                                          .ToArray());
            }
            public string Filters_ToCAML()
            {
                // Create a list of CAML conditions for WildCard filters
                IEnumerable<string> WildcardFilters = Filters
                    .Where(filter => filter.Key.FilterDescriptor.Type == FilterType.Wildcard)
                    .Where(filter => filter.Value != null)
                    .Select(filter => new { 
                        Key = filter.Key as Microsoft.SharePoint.BusinessData.Runtime.WildcardFilter, 
                        Value = Helper.ParseString(filter.Value), 
                        ValueString = filter.Value })
                    .Where(filter => !(filter.Value is string) || (string)filter.Value != String.Empty)
                    .Where(filter => !(filter.Value is DateTime) || (DateTime)filter.Value != new DateTime(1900, 1, 1))
                    .Where(filter => !(filter.Value is Boolean) || (Boolean)filter.Value != false)
                    .Where(filter => List_Fields.Select(x => x.StaticName).Contains(filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name))
                    .Select(filter => String.Format("<Contains><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Contains>", filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name, Helper.CamlEncode(filter.ValueString)));

                // Repeat for Comparison filters
                IEnumerable<string> ComparisonFilters = Filters
                    .Where(filter => filter.Key.FilterDescriptor.Type == FilterType.Comparison)
                    .Where(filter => filter.Value != null)
                    .Select(filter => new { 
                        Key = filter.Key as Microsoft.SharePoint.BusinessData.Runtime.ComparisonFilter, 
                        Value = Helper.ParseString(filter.Value), 
                        ValueString = filter.Value })
                    .Where(filter => !(filter.Value is string) || (string)filter.Value != String.Empty)
                    .Where(filter => List_Fields.Select(x => x.StaticName).Contains(filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name))
                    .Select(filter =>
                          filter.Key.Operator == Microsoft.SharePoint.BusinessData.Runtime.ComparisonFilter.Comparator.Equals
                        ? String.Format("<Eq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Eq>", filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name, Helper.CamlEncode(filter.ValueString))
                        : filter.Key.Operator == Microsoft.SharePoint.BusinessData.Runtime.ComparisonFilter.Comparator.NotEquals
                        ? String.Format("<Neq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Neq>", filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name, Helper.CamlEncode(filter.ValueString))
                        : filter.Key.Operator == Microsoft.SharePoint.BusinessData.Runtime.ComparisonFilter.Comparator.LessThan
                        ? String.Format("<Lt><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Lt>", filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name, Helper.CamlEncode(filter.ValueString))
                        : filter.Key.Operator == Microsoft.SharePoint.BusinessData.Runtime.ComparisonFilter.Comparator.LessThanEquals
                        ? String.Format("<Leq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Leq>", filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name, Helper.CamlEncode(filter.ValueString))
                        : filter.Key.Operator == Microsoft.SharePoint.BusinessData.Runtime.ComparisonFilter.Comparator.GreaterThan
                        ? String.Format("<Gt><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Gt>", filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name, Helper.CamlEncode(filter.ValueString))
                        : filter.Key.Operator == Microsoft.SharePoint.BusinessData.Runtime.ComparisonFilter.Comparator.GreaterThanEquals
                        ? String.Format("<Geq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Geq>", filter.Key.FilterDescriptor.GetTypeDescriptors()[0].Name, Helper.CamlEncode(filter.ValueString))
                        : String.Empty);

                // Join the CAML conditions together
                IEnumerable<string> AllFilters = WildcardFilters.Union(ComparisonFilters);

                // this statement generates the entire set of CAML Query conditions, in the form of <And><FieldRef .../><Value..></Value><And>...</And></And>
                string CAML_Filters = (new StringBuilder())
                                        .Append(AllFilters.Count() > 1 ? "<And>" : String.Empty)
                                        .Append(String.Join("<And>", AllFilters.ToArray()))
                                        .Append(AllFilters.Count() > 1 ? String.Join("</And>", AllFilters.Select(x => String.Empty).ToArray()) : String.Empty)
                                        .ToString();
                if (AllFilters.Count() > 1)
                    CAML_Filters = CAML_Filters.Remove(CAML_Filters.LastIndexOf("<And>"), 5);

                return CAML_Filters;
            }
            public string RowLimit_ToCAML()
            {
                return Filters
                    .Where(filter => filter.Key.FilterDescriptor.Type == FilterType.Limit)
                    //.Where(filter => /*** base   ***/  filter.Value != null && 
                    //                 /*** string ***/  !String.IsNullOrEmpty(filter.Value.ToString()) &&
                    //                 /*** string ***/  filter.Value != new DateTime(1900,1,1).ToString() &&
                    //                 /*** int    ***/  Int32.Parse(filter.Value) != 0)
                    .Select(filter => String.Format("<RowLimit>{0}</RowLimit>", filter.Value))
                    .DefaultIfEmpty(String.Empty)
                    .SingleOrDefault();
            }

            public string CamlQuery()
            {
                return @"
                <View>
                    <Query>
                        <Where>
                            " + Filters_ToCAML() + @"
                        </Where>
                    </Query>
                    <ViewFields>
                        " + Fields_ToCAML() + @"
                    </ViewFields>
                    " + RowLimit_ToCAML() + @"
                </View>";
            }

            private static class Helper
            {
                public static string CamlEncode(string inputStr)
                {
                    return inputStr.Replace("<", "&amp;lt;").Replace(">", "&amp;gt;").Replace("\"", "&amp;quot;");
                }
                public static string CamlDecode(string inputStr)
                {
                    return inputStr.Replace("&lt;", "<").Replace("&gt;", ">").Replace("&quot;", "\"");
                }

                //public static Type GuessType(object obj)
                //{
                //    if (obj is string)
                //        return GuessType(obj as string);
                //    else
                //        return obj.GetType();
                //}
                //public static Type GuessType(string str)
                //{
                //    // Ref: http://stackoverflow.com/questions/5311699/get-datatype-from-values-passed-as-string

                //    Int32 intValue;
                //    Int64 bigintValue;
                //    double doubleValue;
                //    bool boolValue;
                //    DateTime dateValue;

                //    // Place checks higher in if-else statement to give higher priority to type.

                //    if (Int32.TryParse(str, out intValue))
                //        return typeof(Int32);
                //    else if (Int64.TryParse(str, out bigintValue))
                //        return typeof(Int64);
                //    else if (double.TryParse(str, out doubleValue))
                //        return typeof(double);
                //    else if (bool.TryParse(str, out boolValue))
                //        return typeof(bool);
                //    else if (DateTime.TryParse(str, out dateValue))
                //        return typeof(DateTime);
                //    else return typeof(string);

                //}
                public static object ParseString(string str)
                {
                    // Ref: http://stackoverflow.com/questions/5311699/get-datatype-from-values-passed-as-string

                    Int32 intValue;
                    Int64 bigintValue;
                    double doubleValue;
                    bool boolValue;
                    DateTime dateValue;

                    // Place checks higher in if-else statement to give higher priority to type.

                    if (Int32.TryParse(str, out intValue))
                        return intValue;
                    else if (Int64.TryParse(str, out bigintValue))
                        return bigintValue;
                    else if (double.TryParse(str, out doubleValue))
                        return doubleValue;
                    else if (bool.TryParse(str, out boolValue))
                        return boolValue;
                    else if (DateTime.TryParse(str, out dateValue))
                        return dateValue;
                    else return str;

                }
            } // class Helper
        } // class Configuration

        /// <summary>
        /// http://msdn.microsoft.com/en-us/library/microsoft.businessdata.runtime.iadministrablesystem.aspx
        /// Enables custom BDC connectors to report configurable connection properties that can be modified by the administrator.
        /// </summary>
        #region IAdministrableSystem Members

        public IList<AdministrableProperty> AdministrableLobSystemProperties { get { return new List<AdministrableProperty>() { }; } }

        public IList<AdministrableProperty> AdministrableLobSystemInstanceProperties
        {
            get
            {
                return new List<AdministrableProperty>()
                {
                   new AdministrableProperty("SiteURL", "URL of SharePoint site where list exists", typeof(string), "SiteURL", typeof(string), false),
                   new AdministrableProperty("List", "Name of SharePoint list", typeof(string), "List", typeof(string), false),
                   new AdministrableProperty("Credentials", "Type of credentials used to access the list (ANONYMOUS, USER, SYSTEM, or CUSTOM)", typeof(string), "Credentials", typeof(string), false),
                   new AdministrableProperty("CredentialsUsername", "Username for CUSTOM credentials", typeof(string), "CredentialsUsername", typeof(string), false),
                   new AdministrableProperty("CredentialsPassword", "Password for CUSTOM credentials", typeof(string), "CredentialsPassword", typeof(string), false),
                   new AdministrableProperty("CredentialsDomain", "Domain for CUSTOM credentials", typeof(string), "CredentialsDomain", typeof(string), false)
                };
            }
        }

        #endregion

        /// <summary>
        /// http://msdn.microsoft.com/en-us/library/microsoft.businessdata.runtime.isystemutility.aspx
        /// Represents the primary interface implemented by custom connectors.
        /// </summary>
        #region ISystemUtility Members

        public void ExecuteStatic(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, object[] methodSignatureArgs, IExecutionContext context)
        {
            if (methodInstance == null)
            {
                throw (new ArgumentNullException("methodInstance"));
            }
            if (lobSystemInstance == null)
            {
                throw (new ArgumentNullException("lobSystemInstance"));
            }
            if (methodSignatureArgs == null)
            {
                throw (new ArgumentNullException("args"));
            }

            Configuration cfg = GetConfiguration(methodInstance, lobSystemInstance, methodSignatureArgs);
            ExecuteInternal(cfg, methodInstance, methodSignatureArgs);

        }

        public virtual IEnumerator CreateEntityInstanceDataEnumerator(Object rawAdapterEntityInstanceStream, ISharedEntityState sharedEntityState)
        {
            return ((IEnumerable)rawAdapterEntityInstanceStream).GetEnumerator();
        }

        public ITypeReflector DefaultTypeReflector { get { return null; } }

        #endregion

        #region IConnectionManager Members

        public IConnectionManager DefaultConnectionManager { get { return null; } }

        public void Initialize(ILobSystemInstance properties) { }
        public object GetConnection() { return null; }
        public void CloseConnection(object connection) { }
        public void FlushConnections() { }

        #endregion

        #region IDisposable Members

        public void Dispose() { }

        #endregion


        #region private methods (Configuration)
        private Configuration GetConfiguration(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, object[] methodSignatureArgs)
        {
            return new Configuration()
            {
                Credentials = GetCredentials(lobSystemInstance),
                SiteUrl = lobSystemInstance.GetProperties()["SiteURL"] as string,
                ListUrl = lobSystemInstance.GetProperties()["List"] as string,
                Fields = GetFields(methodInstance),
                Filters = GetFilters(methodInstance, methodSignatureArgs)
            };
        }

        private System.Net.ICredentials GetCredentials(ILobSystemInstance lobSystemInstance)
        {
            System.Net.ICredentials outval = null;

            INamedPropertyDictionary data = lobSystemInstance.GetProperties();

            // default credentials setting is user impersonation
            string CredsCfg = "USER";

            // load credentials setting from BDC config file
            if (data.ContainsKey("Credentials"))
                CredsCfg = (string)data["Credentials"];

            // generate output from setting
            switch (CredsCfg)
            {
                case "ANONYMOUS":
                    //outval = null;        // no change
                    break;
                case "USER":
                    // currently logged in user... simple impersonation
                    outval = System.Net.CredentialCache.DefaultCredentials;
                    break;
                case "SYSTEM":
                    // SharePoint service account... SPSecurity makes impersonation easy
                    Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        outval = System.Net.CredentialCache.DefaultCredentials;
                    });
                    break;
                case "CUSTOM":
                    // custom specified account... DotNet impersonation
                    string Username = (string)data["CredentialsUsername"];
                    string Password = (string)data["CredentialsPassword"];
                    string Domain = (string)data["CredentialsDomain"];
                    outval = new System.Net.NetworkCredential(Username, Password, Domain);
                    break;
                default:
                    // something else
                    throw new InvalidDataException("Credentials property value is invalid. Expect 'ANONYMOUS', 'USER', 'SYSTEM', or 'CUSTOM'");
                    break;
            }

            return outval;
        }
        private List<Configuration.Field> GetFields(IMethodInstance methodInstance)
        {
            List<Configuration.Field> outval = new List<Configuration.Field>();

            if (!methodInstance.HasReturnTypeDescriptor)
                throw new NullReferenceException("Method instance has no return type");

            // based on the return TypeDescriptor, select xpath TypeDescriptors/TypeDescriptor and add @name to the outval
            var ReturnTypeDescriptor = methodInstance.GetReturnTypeDescriptor();

            // if the ReturnTypeDescriptor is a collection, obtain its grandchild descriptors; otherwise just child descriptors
            if (ReturnTypeDescriptor.IsCollection)
                ReturnTypeDescriptor.GetChildTypeDescriptors()[0]
                                    .GetChildTypeDescriptors()
                                    .ToList()
                                    .ForEach(x => outval.Add(new Configuration.Field()
                                    {
                                        StaticName = StaticNameEncode(x.Name),
                                        DisplayName = x.Name,
                                        LocalizedName = x.GetLocalizedDisplayName(),
                                        FormatString = x.GetProperties()
                                                        .Where(p => p.Key == "FormatString")
                                                        .Select(p => p.Value as string)
                                                        .DefaultIfEmpty(null)
                                                        .SingleOrDefault()
                                    }));
            else
                ReturnTypeDescriptor.GetChildTypeDescriptors()
                                    .ToList()
                                    .ForEach(x => outval.Add(new Configuration.Field()
                                    {
                                        StaticName = StaticNameEncode(x.Name),
                                        DisplayName = x.Name,
                                        LocalizedName = x.GetLocalizedDisplayName(),
                                        FormatString = x.GetProperties()
                                                        .Where(p => p.Key == "FormatString")
                                                        .Select(p => p.Value as string)
                                                        .DefaultIfEmpty(null)
                                                        .SingleOrDefault()
                                    }));

            return outval;
        }
        private Dictionary<IFilter, string> GetFilters(IMethodInstance methodInstance, object[] methodSignatureArgs)
        {
            List<IFilter> Filters = methodInstance.GetFilters().ToList();

            return Filters.ToDictionary(filter => filter,
                                        filter => methodSignatureArgs[Filters.IndexOf(filter)] == null
                                                  ? String.Empty
                                                  : methodSignatureArgs[Filters.IndexOf(filter)]
                                                        .ToString().TrimStart('*').TrimEnd('*'));

            //return outval;
        }
        #endregion

        #region private methods (Execution)
        private void ExecuteInternal(Configuration config, IMethodInstance methodInstance, object[] args)
        {
            switch (methodInstance.MethodInstanceType)
            {
                case MethodInstanceType.Finder:
                    ExecuteFinder(config, methodInstance, args);
                    break;

                case MethodInstanceType.SpecificFinder:
                    ExecuteSpecificFinder(config, methodInstance, args);
                    break;

                case MethodInstanceType.StreamAccessor: 
                    ExecuteStreamAccessor(config, methodInstance, args);
                    break;

                case MethodInstanceType.IdEnumerator:
                    ExecuteIdEnumerator(config, methodInstance, args);
                    break;

                default:
                    throw new NotImplementedException("Method Not Implemented [" + methodInstance.MethodInstanceType.ToString() + "]");
                    //break;
            }
        }

        private void ExecuteFinder(Configuration config, IMethodInstance methodInstance, object[] args)
        {
            List<DynamicType> returnParameters = new List<DynamicType>();
            foreach (var record in GetAllRecords(config))
            {
                DynamicType dType = new DynamicType(methodInstance.GetReturnTypeDescriptor().Name);
                foreach (KeyValuePair<string, object> ListItemValue in record.Value)
                {
                    dType.Add(ListItemValue.Key, ListItemValue.Value);
                }
                returnParameters.Add(dType);
            }
            args[args.GetUpperBound(0)] = returnParameters.ToArray();
        }

        private void ExecuteSpecificFinder(Configuration config, IMethodInstance methodInstance, object[] args)
        {
            var record = GetSingleRecord(config).Single();

            DynamicType dType = new DynamicType(methodInstance.GetReturnTypeDescriptor().Name);
            foreach (KeyValuePair<string, object> ListItemValue in record.Value)
            {
                dType.Add(ListItemValue.Key, ListItemValue.Value);
            }
            args[args.GetUpperBound(0)] = dType;
        }

        private void ExecuteStreamAccessor(Configuration config, IMethodInstance methodInstance, object[] args)
        {
            args[args.GetUpperBound(0)] = GetStream(config);
        }

        private void ExecuteIdEnumerator(Configuration config, IMethodInstance methodInstance, object[] args)
        {
            List<DynamicType> returnParameters = new List<DynamicType>();
            foreach (var ListItem in GetAllRecords(config))
            {
                DynamicType dType = new DynamicType(methodInstance.GetReturnTypeDescriptor().Name);
                foreach (KeyValuePair<string, object> ListItemValue in ListItem.Value)
                {
                    dType.Add(ListItemValue.Key, ListItem.Value);
                }
                returnParameters.Add(dType);
            }
            args[args.GetUpperBound(0)] = returnParameters.ToArray();
        }
        #endregion

        #region private methods (SharePoint ListItem retrieval)

        /// <summary>
        /// Get All Rows in a List
        /// </summary>
        /// <param name="url">This is the URL of list to get the rows from</param>
        /// <param name="networkcredential">Network Credentials</param>
        /// <returns>Dictionary object defined as:
        /// Dictionary&lt;int, Dictionary&lt;string, object&gt;&gt;
        /// where int = the id of the row
        /// string = column name
        /// object = column value</returns>
        private Dictionary<int, Dictionary<string, object>> GetAllRecords(Configuration _config)
        {
            Dictionary<int, Dictionary<string, object>> retList = new Dictionary<int, Dictionary<string, object>>();
            string strErrorColumnNotFound = "Error: Column Not Found";

            //Create Client Context
            Microsoft.SharePoint.Client.ClientContext ctx = _config.ClientContext;

            //Get the list
            var oList = _config.List;

            // Create the CamlQuery object
            Microsoft.SharePoint.Client.CamlQuery camlquery = new Microsoft.SharePoint.Client.CamlQuery();
            camlquery.ViewXml = _config.CamlQuery();

            //Load the Caml Query into the list item collection
            ListItemCollection collListItem = oList.GetItems(camlquery);

            //Load the list item collection into the client context
            ctx.Load(collListItem);

            //Execute query to get list items
            ctx.ExecuteQuery();

            retList = collListItem.ToDictionary(listitem => listitem.Id,
                                                listitem => _config.Fields
                                                                   .ToDictionary(LookupField => LookupField.DisplayName,
                                                                                 LookupField => _config.List_Fields.Any(x => x.StaticName == LookupField.StaticName) 
                                                                                                ? String.IsNullOrEmpty(LookupField.FormatString)
                                                                                                  ? listitem[LookupField.StaticName]
                                                                                                  : String.Format(LookupField.FormatString, listitem[LookupField.StaticName])
                                                                                                : strErrorColumnNotFound));

            //Dispose of the client context
            ctx.Dispose();

            return retList;

        }

        /// <summary>
        /// Get All Rows in a List
        /// </summary>
        /// <returns>Dictionary object defined as:
        /// Dictionary&lt;int, Dictionary&lt;string, object&gt;&gt;
        /// where int = the id of the row
        /// string = column name
        /// object = column value</returns>
        private Dictionary<int, Dictionary<string, object>> GetSingleRecord(Configuration _config)
        {
            Dictionary<int, Dictionary<string, object>> retList = new Dictionary<int, Dictionary<string, object>>();
            //Dictionary<string, string> fieldNames = new Dictionary<string, string>();
            string strErrorColumnNotFound = "Error: Column Not Found";

            //Create Client Context
            Microsoft.SharePoint.Client.ClientContext ctx = _config.ClientContext;

            //Get the list
            var oList = _config.List;

            // Create the CamlQuery object
            Microsoft.SharePoint.Client.CamlQuery camlquery = new Microsoft.SharePoint.Client.CamlQuery();
            camlquery.ViewXml = _config.CamlQuery();

            //Load the Caml Query into the list item collection
            ListItemCollection collListItem = oList.GetItems(camlquery);

            //Load the list item collection into the client context
            ctx.Load(collListItem);

            //Execute query to get list items
            ctx.ExecuteQuery();

            retList = collListItem.ToDictionary(listitem => listitem.Id,
                                                listitem => _config.Fields
                                                                   .ToDictionary(LookupField => LookupField.DisplayName,
                                                                                 LookupField => _config.List_Fields.Any(x => x.StaticName == LookupField.StaticName)
                                                                                                ? String.IsNullOrEmpty(LookupField.FormatString)
                                                                                                  ? listitem[LookupField.StaticName]
                                                                                                  : String.Format(LookupField.FormatString, listitem[LookupField.StaticName])
                                                                                                : strErrorColumnNotFound));

            //Dispose of the client context
            ctx.Dispose();

            return retList;

        }

        private System.IO.Stream GetStream(Configuration _config)
        {
            System.IO.Stream outval = null;

            //Create Client Context
            Microsoft.SharePoint.Client.ClientContext ctx = _config.ClientContext;

            //Get the list
            var oList = _config.List;

            // Create the CamlQuery object
            Microsoft.SharePoint.Client.CamlQuery camlquery = new Microsoft.SharePoint.Client.CamlQuery();
            camlquery.ViewXml = _config.CamlQuery();

            //Load the Caml Query into the list item collection
            ListItemCollection collListItem = oList.GetItems(camlquery);

            //Load the list item collection into the client context
            ctx.Load(collListItem);

            //Execute query to get list items
            ctx.ExecuteQuery();

            outval = Microsoft.SharePoint.Client.File.OpenBinaryDirect(ctx, collListItem.Single().File.ServerRelativeUrl).Stream;

            //Dispose of the client context
            ctx.Dispose();

            return outval;
        }

        #endregion


        #region Helper Methods

        public static string StaticNameEncode(string inp)
        {
            // Define the Allowed Characters (particularly, allow 0-9 and underscore)
            const string AllowedChars = @"[A-Za-z0-9_]";
            System.Text.RegularExpressions.Regex AllowedCharsRegex = new System.Text.RegularExpressions.Regex(AllowedChars);

            // for each character in the string, check whether it needs to be encoded.. if so, encode and ToLower
            return String.Join("",
                               inp.ToList()     // split into array of characters
                                  .Select(c => AllowedCharsRegex.Matches(c.ToString()).Count > 0
                                               ? c.ToString()
                                               : String.Format("_x{0:x4}_", System.Convert.ToUInt32(c))
                                               )
                                  .ToArray()    // convert IEnumerable to [] for String.Join
                              );
        }

        #endregion

    } // class SPListConnector
} // namespace
